import java.sql.*;
/*
 * Proposed Functions:
* Add Student
* Deleting Student
* Find Class by name
* Find all students with a GPA in a given range.
* Find all courses for a given major.
* Find all courses for a given semester.
* Find classes that start at a given time
* Find all classes in a given location.
* Find grad year of a student given their sid.
* Function to change the graduation of a student (satisfies update requirement)
 */
public class Database {
	private static Connection conn;
	
	public static void connectToDatabase() {
		System.out.println("Connecting to database. This may take a long time." +
				"\nI suggest you multitask and work on something else.");
		
		try { 
            Class.forName("oracle.jdbc.driver.OracleDriver"); 
        } catch (ClassNotFoundException e) { 
            System.out.println("Could not load the driver"); 
        } 
		
		try {
			conn = DriverManager.getConnection("jdbc:oracle:thin:@oracle1.cs.utsa.edu:1521:xe", "tbrab374", "utsa0398");
		} catch (SQLException e) {
			System.out.println("SQL Connect error. See stack trace.");
			e.printStackTrace();
			System.exit(0);
		}
		
		System.out.println("Connect successful.");
	}
	
	/* name is an object type */
	/* seems to be working */
	public static void addStudent(int sid, String fname, String lname, String major, int credits, double GPA) throws SQLException {
		 Statement stmt = null;
         try {
            stmt = conn.createStatement();
            stmt.executeUpdate(
              "insert into Students " +
              "values(" + sid + "," +
              "name_t('" + fname + "','" + lname + "')," +
              "'" + major + "'," +
              credits + "," +
              GPA + ")");
            System.out.println("Student " + fname + " " + lname + "has been added.");
         } catch (SQLException e) {
            System.out.println("Exception in Database.addStudent");
            e.printStackTrace();
         } finally {
            if (stmt != null) { stmt.close(); }
         }		
	}
	
	/* seems to be working */
	public static void delStudent(int sid) throws SQLException {
		 Statement stmt = null;
         try {
            stmt = conn.createStatement();
            stmt.executeUpdate(
              "delete from Students " +
              "where SID = " + sid);
            System.out.println("Student" + sid +" has been deleted.");
         } catch (SQLException e) {
            System.out.println("Exception in Database.delStudent");
            e.printStackTrace();
         } finally {
            if (stmt != null) { stmt.close(); }
         }		
	}
	
	/* seems to be working */
	public static void findClassByName(String name) throws SQLException {
		Statement stmt = null;
		String query = "select CNO, Title " +
                "from Courses " +
                "where Title like '%" + name + "%'";

		try {
		   stmt = conn.createStatement();
		   ResultSet rs = stmt.executeQuery(query);

		   System.out.println("CNO\tTitle");
		   while (rs.next()) {
			  String cno = rs.getString("CNO");
		      String title = rs.getString("Title");		  
			  System.out.println(cno + "\t" + title);
		   }
		} catch (SQLException e) {
			System.out.println("Exception in Database.findClassByName");
			e.printStackTrace();
		} finally {
			if (stmt != null) { stmt.close(); }
		}
	}
	
	/* name is a name_t object so needs to be referenced differently */
	public static void listAllStudentsByGPARange(double lowest, double highest) throws SQLException {
		Statement stmt = null;
        String query = "select Name, SID, GPA " +
                       "from Students " +
                       "where GPA >= " + lowest + " and GPA <= " + highest;

        try {
           stmt = conn.createStatement();
           ResultSet rs = stmt.executeQuery(query);

           System.out.println("SID\tGPA\tName");
           while (rs.next()) {
        	   //all this just to find names. Oi.
        	 java.sql.Struct jdbcStruct = (Struct) rs.getObject("Name");
        	 Object[] nameAttributes = jdbcStruct.getAttributes();
        	 String name = nameAttributes[0].toString() + "\t" + nameAttributes[1].toString();
             int studentID = rs.getInt("SID");
             float gpa = rs.getFloat("GPA");
             System.out.println(studentID + "\t" + gpa + "\t" + name);
           }
        } catch (SQLException e) {
           System.out.println("Exception in Database.listAllStudentsByGPARange");
           e.printStackTrace();
        } finally {
           if (stmt != null) { stmt.close(); }
        }	
	}	

	/* seems to be working */
	public static void findCoursesByMajor (String majorID) throws SQLException {
		majorID = majorID.toUpperCase();
		 Statement stmt = null;
         String query = "select CNO, Title " +
                        "from Courses " +
                        "where Dept = '" + majorID + "'";

         try {
            stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(query);

            System.out.println("CNO\tTitle");
            while (rs.next()) {
              String cno = rs.getString("CNO");
              String title = rs.getString("Title");
              System.out.println(cno + "\t" + title);
            }
         } catch (SQLException e) {
           System.out.println("Exception in Database.findCoursesByMajor");
           e.printStackTrace();
         } finally {
           if (stmt != null) { stmt.close(); }
         }		
	}
	
	/* semester is object type sem and needs to be referenced differently */
	/* not working currently, semester is referenced incorrectly */
	/* JDBC may also not play well with the alias syntax */
	public static void findCoursesBySemester(String semcode) throws SQLException {		
		 Statement stmt = null;
		 
		 String season = semcode.substring(0, 2).toUpperCase();
		 String yr = "2012";
         String query = "select C.CNO, C.Title, S.SECNO, S.Semester " +
                        "from Courses C, Sections S " + 
                        "where C.CNO = S.CNO " +
                        "and S.Semester.season LIKE '" + season + "'";
		 if(semcode.length() > 2){
			 yr = semcode.substring(2);
			 query += "and S.Semester.year LIKE '" + yr + "'";
		 }
		 

         try {
            stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(query);
            System.out.println("All courses in the " + season+ " semester:");
            while (rs.next()) {
               String cno = rs.getString("CNO");
               String title = rs.getString("Title");
               String secno = rs.getString("SECNO");
               System.out.println(cno + ": " + title + "\n\tSection: " + secno);
            }
         } catch (SQLException e) {
           System.out.println("Exception in Database.findCoursesBySemester");
           e.printStackTrace();
         } finally {
           if (stmt != null) { stmt.close(); }
         }							
	}
	
	/* does not check for valid input */
	/* seems to be working */
	public static void findClassesStartingAt(int hour) throws SQLException {
		//hour should be between 0 and 23, inclusive.
		//Classes are assumed to be one hour long each, and available at any point in time.
		
		Statement stmt = null;
        String query = "select C.CNO, C.Title, S.SECNO, S.Time " +
                "from Courses C, Sections S " +
                "where C.CNO = S.CNO " +
                "and S.Time like '%";

        if (hour < 10) { query += "0"; } // prefix single digit hours with 0
        query += hour + "%'";

        try {
           stmt = conn.createStatement();
           ResultSet rs = stmt.executeQuery(query);

           System.out.println("CNO\tSection\tTime\t\tTitle");
           while (rs.next()) {
        	   String cno = rs.getString("CNO");
               String title = rs.getString("Title");
               String secno = rs.getString("SECNO");
               String time = rs.getString("Time");
               System.out.println(cno + "\t" + secno + "\t" + time + "\t" + title);
           }
        } catch (SQLException e) {
          System.out.println("Exception in Database.findClassesStartingAt");
          e.printStackTrace();
        } finally {
          if (stmt != null) { stmt.close(); }
        }							
	}
	
	/* untested */
	public static void findAllClassesIn(String buildingID) throws SQLException {
		Statement stmt = null;
		buildingID = buildingID.toUpperCase();
		String query = "select Sections.CNO, Title, Room " +
		               "from Sections left join Courses " +
				       "on Sections.CNO = Courses.CNO " +
		               "where Room like '%" + buildingID + "%'";
		
		try {
            stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(query);

            System.out.println("Room\tCNO\tTitle");
            while (rs.next()) {
              String cno = rs.getString("CNO");
              String title = rs.getString("Title");
              String room = rs.getString("Room");
              System.out.println(room + "\t" + cno + "\t" + title);
            }
         } catch (SQLException e) {
           System.out.println("Exception in Database.findAllClassesIn");
           e.printStackTrace();
         } finally {
           if (stmt != null) { stmt.close(); }
         }		
	}
	
	/* No students have grad years. This will return no results.*/
	public static void findStudentGradYear(int sid) throws SQLException {
		Statement stmt = null;
        String query = "select Grad_Year " +
                       "from Degree_Plan, Students " +
                       "where PID = SID " + 
                       "and SID = " + sid;

      try {
         stmt = conn.createStatement();
         ResultSet rs = stmt.executeQuery(query);

         while (rs.next()) {
            int gradyear = rs.getInt("Grad_Year");
            System.out.println(gradyear);
         }
      } catch (SQLException e) {
        System.out.println("Exception in Database.findStudentGradYear");
        e.printStackTrace();
      } finally {
        if (stmt != null) { stmt.close(); }
      }											
	}
	
	/* This produced errors last time I tried it. */
	public static void changeGraduation(int sid, int newYear) throws SQLException {
		 Statement stmt = null;
         try {
            stmt = conn.createStatement();
            stmt.executeUpdate(
              "Update Degree_Plan " +
              "set Degree_Plan.Grad_Year = " + newYear + " " +
              "where Degree_Plan.PID = Students.SID " +
              "and SID = " + sid);
         } catch (SQLException e) {
            System.out.println("Exception in Database.delStudent");
            e.printStackTrace();
         } finally {
            if (stmt != null) { stmt.close(); }
         }		
	}
	
}
